﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Util.SQLHelpers;

namespace AllSqlHelperWin.SQLHelperForms.OracleForms
{
    public partial class OracleHelperForm : Form
    {
        #region 变量
        #endregion

        public OracleHelperForm()
        {
            InitializeComponent();
        }

        private void MysqlHelperForm_Load(object sender, EventArgs e)
        {
            try
            {
                cmbClass.SelectedIndex = 0;
                dtTStart.Text = DateTime.Now.AddDays(-30).ToString();
                dtTEnd.Text = DateTime.Now.AddDays(1).ToString();
                //MysqlHelper.Initial("Server=localhost;Port=3306;Database=testdb;Uid=root;Pwd=Aa123456;");  // 可选
            }
            catch (Exception ex)
            {
                MessageBox.Show("初始化页面报错！错误信息：" + ex.Message);
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                #region 
                string sql = @"select 
                Id as 主键,
                Name as 名字,
                Class as 班级,
                Age as 年龄,
                if(Gender=true,""男"",""女"") as 性别,
                CreateTime as 创建时间
                 from User where 1=1";

                List<OracleParameter> sqlParameter = new List<OracleParameter>();

                string name = txtName.Text.Trim();
                if (!string.IsNullOrEmpty(name))
                {
                    sql += " and Name = @Name";
                    sqlParameter.Add(new OracleParameter("@Name", name));
                }

                bool isGender_Boy = rdBtnGenderB.Checked;
                sql += " and Gender = @Gender";
                sqlParameter.Add(new OracleParameter("@Gender", isGender_Boy));

                string userClass = cmbClass.Text.Trim();
                if (!string.IsNullOrEmpty(userClass) && userClass != "全部")
                {
                    sql += " and Class = @Class";
                    sqlParameter.Add(new OracleParameter("@Class", userClass));
                }

                string dtStart = dtTStart.Text.Trim();
                if (!string.IsNullOrEmpty(dtStart))
                {
                    sql += " and CreateTime > @dtStart";
                    sqlParameter.Add(new OracleParameter("@dtStart", Convert.ToDateTime(dtStart)));
                }

                string dtEnd = dtTEnd.Text.Trim();
                if (!string.IsNullOrEmpty(dtEnd))
                {
                    sql += " and CreateTime < @dtEnd";
                    sqlParameter.Add(new OracleParameter("@dtEnd", Convert.ToDateTime(dtEnd)));
                }

                // 执行sql
                DataSet dt1 = OracleSqlHelper.SqlDataAdapter_Fill(sql, sqlParameter.ToArray());  // 
                #endregion
                //string sql = $"SELECT * FROM `user` WHERE `Name`='{txtName.Text.Trim()}' AND Age='145';";
                //DataSet dt1 = OracleSqlHelper.SqlDataAdapter_Fill(sql, null);

                if (dt1 != null && dt1.Tables.Count > 0)
                {
                    dataGridView1.DataSource = dt1?.Tables[0]?.DefaultView;
                }
                else
                {
                    dataGridView1.DataSource = new DataTable().DefaultView;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询信息失败！错误信息：" + ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                #region 
                string sql = @"select 
                Id as 主键,
                Name as 名字,
                Class as 班级,
                Age as 年龄,
                if(Gender=true,""男"",""女"") as 性别,
                CreateTime as 创建时间
                 from User where 1=1";

                List<OracleParameter> sqlParameter = new List<OracleParameter>();

                string name = txtName.Text.Trim();
                if (!string.IsNullOrEmpty(name))
                {
                    sql += " and Name = @Name";
                    sqlParameter.Add(new OracleParameter("@Name", name));
                }

                bool isGender_Boy = rdBtnGenderB.Checked;
                sql += " and Gender = @Gender";
                sqlParameter.Add(new OracleParameter("@Gender", isGender_Boy));

                string userClass = cmbClass.Text.Trim();
                if (!string.IsNullOrEmpty(userClass) && userClass != "全部")
                {
                    sql += " and Class = @Class";
                    sqlParameter.Add(new OracleParameter("@Class", userClass));
                }

                string dtStart = dtTStart.Text.Trim();
                if (!string.IsNullOrEmpty(dtStart))
                {
                    sql += " and CreateTime > @dtStart";
                    sqlParameter.Add(new OracleParameter("@dtStart", Convert.ToDateTime(dtStart)));
                }

                string dtEnd = dtTEnd.Text.Trim();
                if (!string.IsNullOrEmpty(dtEnd))
                {
                    sql += " and CreateTime < @dtEnd";
                    sqlParameter.Add(new OracleParameter("@dtEnd", Convert.ToDateTime(dtEnd)));
                }
                #endregion

                // 执行sql
                DataTable dt1 = OracleSqlHelper.ExecuteReader(sql, sqlParameter.ToArray());
                if (dt1 != null)
                {
                    dataGridView1.DataSource = dt1.DefaultView;
                }
                else
                {
                    dataGridView1.DataSource = new DataTable().DefaultView;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询信息失败！错误信息：" + ex.Message);
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        private void 添加ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            OracleAddForm mysqlAddForm = new OracleAddForm();
            mysqlAddForm.ShowDialog();
        }

        /// <summary>
        /// 更新
        /// </summary>
        private void 更新ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                //判断是否选中单元格
                if (dataGridView1.SelectedRows.Count > 0)//有选中
                {
                    UserModel userModel = new UserModel();
                    userModel.Id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value);
                    userModel.Name = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                    userModel.Class = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                    userModel.Age = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[3].Value);
                    string genderStr = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                    userModel.Gender = genderStr == "男" ? true : false;
                    userModel.CreateTime = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[5].Value);

                    OracleUpdateForm mysqlUpdateForm = new OracleUpdateForm(userModel);
                    mysqlUpdateForm.ShowDialog();
                }
                else
                {
                    MessageBox.Show("请选择任意一行！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("更新行出错！错误信息：" + ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                //判断是否选中单元格
                if (dataGridView1.SelectedRows.Count > 0)//有选中
                {
                    List<int> ids = new List<int>();

                    string idStr = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                    int id = Convert.ToInt32(idStr);

                    int delResult = OracleSqlHelper.ExecuteNonQuery("delete from User where id = @Id", new OracleParameter[] { new OracleParameter("@Id", id) });

                    if (delResult > 0)
                    {
                        MessageBox.Show("删除行成功！主键：" + idStr);
                    }
                    else
                    {
                        MessageBox.Show("删除行失败！主键：" + idStr);
                    }
                }
                else
                {
                    MessageBox.Show("请选择任意一行！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("删除行出错！错误信息：" + ex.Message);
            }
        }
    }
}